package com.whu.tcx01.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;


public class CRUD {
    public static int selectNode(double x, double y) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtils.getConnect();
            String sql = "select nodeid from nodes where x = ? and y = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1, x);
            preparedStatement.setDouble(2, y);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            } else {
                return -1;
            }
        } catch (Exception e) {
            System.out.print(e.toString());
            return -1;
        } finally {
            release(connection, preparedStatement);
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    public static long insertNode(double x, double y) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtils.getConnect();
            String sql = "insert into nodes(x,y,point) values(?,?,ST_GeomFromText(?,4326))";
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setDouble(1, x);
            preparedStatement.setDouble(2, y);
            preparedStatement.setString(3, "POINT(" + x + " " + y + ")");
            preparedStatement.execute();

            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getLong(1);
            }
            return -1;
        } catch (Exception e) {
            System.out.print(e.toString());
            return -1;
        } finally {
            release(connection, preparedStatement);
        }

    }

    private static void release(Connection connection, PreparedStatement preparedStatement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static long insertRoad(long start, long stop, int direction, double distance) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtils.getConnect();
            String sql = "insert into roads(start,stop,direction,distance) values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setLong(1, start);
            preparedStatement.setLong(2, stop);
            preparedStatement.setInt(3, direction);
            preparedStatement.setDouble(4, distance);
            preparedStatement.execute();

            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getLong(1);
            }
            return -1;
        } catch (Exception e) {
            System.out.print(e.toString());
            return -1;
        } finally {
            release(connection, preparedStatement);
        }
    }

    public static long insertLine(long roadId, int direction, String list) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtils.getConnect();
            String sql = "insert into lines(roadid,direction,list,line) values(?,?,?,ST_GeomFromText(?,4326))";
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setLong(1, roadId);
            preparedStatement.setInt(2, direction);
            preparedStatement.setString(3, list);
            String wkt = list.replace(",", " ").replace(";", ",");
            preparedStatement.setString(4, "LINESTRING(" + wkt + ")");
            preparedStatement.execute();

            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getLong(1);
            }
            return -1;
        } catch (Exception e) {
            System.out.print(e.toString());
            return -1;
        } finally {
            release(connection, preparedStatement);
        }
    }


    //读取car1_g表
    public static void getCarPoints(List<List<Double>> ObservationProbListList, List<List<String>> candidateCoords) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<String> outPutCoords = new ArrayList();

        int flag = 1;
        try {
            connection = DbUtils.getConnect();
            String sql = "select * from car1_g";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("======第 "+ flag +" 个轨迹点======");
                getRoadsNearby(resultSet.getDouble(11),resultSet.getDouble(12),connection,ObservationProbListList, candidateCoords,flag);
                outPutCoords.add(resultSet.getDouble(11)+","+resultSet.getDouble(12));
                flag++;
//                System.out.println(resultSet.getInt(1) + " " + resultSet.getInt(2)+ " " + resultSet.getInt(3) + " " + resultSet.getInt(4));
            }
            SaveAsTxt.exportTxt5(outPutCoords,"original");

        } catch (Exception e) {
            System.out.print(e.toString());
        } finally {
            release(connection, preparedStatement);
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    //搜索临近路线
    public static void getRoadsNearby(Double start, Double stop,Connection connection, List<List<Double>> ObservationProbListList,List<List<String>> candidateCoords , int flag) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<String> CoorList = new ArrayList<>();//记录线段端点坐标
        List<Double> DisList = new ArrayList<>();//记录距离
//        int num = 0;
        List<String> outPutCoords = new ArrayList();//记录垂足点

        try {
            String sql = "select lineid,roadid,direction,list,line " +
                    "from lines " +
                    "where st_intersects(st_makeenvelope(" + start + " - 0.001," + stop + " - 0.001," + start + "+ 0.001," + stop + " + 0.001,4326), line)";
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();

            while (rs.next()) {
//                num++;
                CoorList.add(rs.getString(4).replace(";",","));
            }

//            System.out.println(CoorList.size());
            //去除重复
//            CoorList = ListSet.removeRepeat2(CoorList);
            for (int i = 0; i < CoorList.size(); i++) {
//                System.out.println(CoorList.get(i));
                String[] strList = CoorList.get(i).split(",");
//                System.out.println(start+","+stop+","+Double.parseDouble(strList[0])+","+Double.parseDouble(strList[1])+","+Double.parseDouble(strList[2])+","+Double.parseDouble(strList[3]));
                //记录返回的垂足
                Double[] coords = PointUtil.Foot2(start,stop,Double.parseDouble(strList[0]),Double.parseDouble(strList[1]),Double.parseDouble(strList[2]),Double.parseDouble(strList[3]));
//                PointUtil.Foot(start,stop,Double.parseDouble(strList[0]),Double.parseDouble(strList[1]),Double.parseDouble(strList[2]),Double.parseDouble(strList[3]));
                outPutCoords.add(coords[0]+","+coords[1]);
                //计算的候选点到轨迹点的距离
                DisList.add(PointUtil.Distance(start,stop,coords));
            }

            candidateCoords.add(outPutCoords);
//            SaveAsTxt.exportTxt5(CoorList,"CoorList");
            SaveAsTxt.exportTxt5(outPutCoords,"outPutCoords"+flag);

            ObservationProbListList.add(getObservationP(DisList));
//            System.out.println("共"+num+"个");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //计算观测概率
    private static List<Double> getObservationP(List<Double> disList) {
        //采用标准正态分布，无偏即 均值 = 0 标准差 σ=20
        double pi = Math.PI; //圆周率
        double e = Math.E;//自然对数
        double dp = 20; //i
        List<Double> OList = new ArrayList<>();
        for (int i = 0; i < disList.size(); i++) {
            Double o = (1/(Math.sqrt(2*pi)*dp))*Math.pow(e,-(disList.get(i)*disList.get(i)/(2*dp*dp)));
            OList.add(o);
        }
        return OList;
    }

    //读取car1_g表
    public static void getCarPoints2(List<List<Double>> TransmissionProbList, List<List<String>> tempCandListList,
                                     List<List<Double>> tempTrackCandDis, List<Double> tempMinDis, List<Double> tempTrackPointDis,
                                     List<Double> TransProbList, List<Integer> breakPoints){

        List<String> coorList = new ArrayList<>();//记录轨迹点
        List<List<Double>> trackPointDis = new ArrayList<>();//记录轨迹点之间的距离
        List<List<String>> candListList = new ArrayList<>();//记录相邻轨迹点的候选点
        List<List<Double>> trackCandDis = new ArrayList<>();//记录相邻轨迹点的候选点之间的最短长度
        List<List<Double>> minDis = new ArrayList<>();//记录每组候选点之间，最小距离

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

//        int flag = 0;

        try {
            connection = DbUtils.getConnect();
            String sql = "select * from car1_g";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
//                System.out.println("======第 "+ flag +" 个轨迹点======");
                List<String> tempArr = new ArrayList<>();
                getRoadsNearby2(resultSet.getDouble(11),resultSet.getDouble(12),connection,tempArr);

                candListList.add(tempArr);
                tempCandListList.add(tempArr);
                coorList.add(resultSet.getDouble(11)+","+resultSet.getDouble(12));
//                flag++;
            }

            //计算所有相邻轨迹点的候选点之间的长度
            trackCandDis = getCandDistance(candListList);

            int front = 0;//记录当前位置
            int now = 0;
            int end = trackCandDis.size();

            for (int i = 0; i <= trackCandDis.size(); i++) {
                List<List<String>> tempC = new ArrayList<>(candListList);
                List<String> coorL = new ArrayList<>(coorList);

                if(i == trackCandDis.size()){
                    int f = i+1;
                    System.out.println("第" + f + "个点是终止点。");
                    breakPoints.add(i);
                    tempTrackCandDis.clear();
                    tempMinDis.clear();
                    tempTrackPointDis.clear();

                    tempCandListList = tempC.subList(front, end+1);

                    //计算当前路段相邻轨迹点的候选点之间的长度
                    tempTrackCandDis = getCandDistance(tempCandListList);

                    List<String> tempCoorList = coorL.subList(front, end+1);

                    TransProbList = TranProbUtil.calTranProbUtil(tempTrackCandDis, tempMinDis, tempTrackPointDis, tempCoorList);
                    List<Double> tList = new ArrayList<>();
                    tList.addAll(TransProbList);
                    front = now;
                    front++;
                    now++;

                    //保存为txt
                    SaveAsTxt.exportTxt2(tList,i);

                    minDis.add(tempMinDis);
                    trackPointDis.add(tempTrackPointDis);
                    TransmissionProbList.add(tList);
                    TransProbList.clear();

                }else {
                    List<Double> tempList = trackCandDis.get(i);

                    if((tempList.size() == 0 && now < end)) {
                        breakPoints.add(i);
                        int f = i+1;

                        System.out.println("第" + f + "个点未匹配到候选点,打断！");
                        tempTrackCandDis.clear();
                        tempMinDis.clear();
                        tempTrackPointDis.clear();

                        if((now - front) > 1){
                            tempCandListList = tempC.subList(front, now);

                            //计算当前路段相邻轨迹点的候选点之间的长度
                            tempTrackCandDis = getCandDistance(tempCandListList);

                            List<String> tempCoorList = coorL.subList(front, now);
                            TransProbList = TranProbUtil.calTranProbUtil(tempTrackCandDis, tempMinDis, tempTrackPointDis, tempCoorList);
                            List<Double> tList = new ArrayList<>();
                            tList.addAll(TransProbList);
                            front = now;
                            front++;
                            now++;

                            //保存为txt
                            SaveAsTxt.exportTxt2(tList,i);

                            minDis.add(tempMinDis);
                            trackPointDis.add(tempTrackPointDis);
                            TransmissionProbList.add(tList);
                            TransProbList.clear();
//                        System.out.println(tList.get(0));
                            continue;
                        }else if((now - front) < 1 || (now - front) == 1){
                            breakPoints.add(i);
                            front = now;
                            front++;
                            now++;
                            minDis.add(tempMinDis);
                            trackPointDis.add(tempTrackPointDis);
                            TransmissionProbList.add(TransProbList);
                            TransProbList.clear();
                            continue;
                        }
                    }
                    now++;
                }
            }
        } catch (Exception e) {
            System.out.print(e.toString());
        } finally {
            release(connection, preparedStatement);
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    //计算转移概率
    public static List<Double> getTransmissionP(List<Double> tempTrackPointDis, List<Double> tempMinDis) {
//        System.out.println(tempTrackPointDis.size()+" "+tempMinDis.size());
        List<Double> TransProbList = new ArrayList<>();
        for (int i = 0; i < tempTrackPointDis.size(); i++) {
            double temp = tempTrackPointDis.get(i) / tempMinDis.get(i);
            TransProbList.add(temp);
        }
        return TransProbList;
    }


    //搜索临近路线
    public static void getRoadsNearby2(Double start, Double stop,Connection connection, List<String> tempArr) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<String> CoorList = new ArrayList<>();//记录坐标
        List<Double> DisList = new ArrayList<>();//记录距离
//        int num = 0;
        try {
            String sql = "select lineid,roadid,direction,list,line " +
                    "from lines " +
                    "where st_intersects(st_makeenvelope(" + start + " - 0.001," + stop + " - 0.001," + start + "+ 0.001," + stop + " + 0.001,4326), line)";
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();

            while (rs.next()) {
//                num++;
                CoorList.add(rs.getString(4).replace(";",","));
            }
            //去除重复
//            CoorList = ListSet.removeRepeat(CoorList);

            for (String s : CoorList) {
//                System.out.println(CoorList.get(i));
                String[] strList = s.split(",");
//                System.out.println(start+","+stop+","+Double.parseDouble(strList[0])+","+Double.parseDouble(strList[1])+","+Double.parseDouble(strList[2])+","+Double.parseDouble(strList[3]));
                Double[] coords = PointUtil.Foot2(start,stop,Double.parseDouble(strList[0]),Double.parseDouble(strList[1]),Double.parseDouble(strList[2]),Double.parseDouble(strList[3]));
                tempArr.add(coords[0] + ";" + coords[1]);
            }
//            System.out.println("共"+num+"个");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //轨迹点之间的距离
    public static List<Double> getCarDistance(List<String> coorList) {
        Double[] xArr = new Double[coorList.size()];
        Double[] yArr = new Double[coorList.size()];
        List<Double> disList = new ArrayList<>();

        for (int i = 0; i < coorList.size(); i++) {
            String str = coorList.get(i);
            String[] strArr = str.split(",");
            xArr[i]= Double.valueOf(strArr[0]);
            yArr[i]= Double.valueOf(strArr[1]);
        }
        disList = PointUtil.CalTrackPointDistance(xArr,yArr);
        return disList;
    }

    //计算相邻轨迹点之间的候选点之间的距离集合
    public static List<List<Double>> getCandDistance(List<List<String>> candListList) {
        List<List<Double>> cDisList = new ArrayList<>();

        for (int i = 1; i < candListList.size(); i++) {
            List<Double> cDis = new ArrayList<>();//存放距离
            List<String> canList1 = candListList.get(i-1);
            List<String> canList2 = candListList.get(i);
            for (String s : canList2) {
                String[] canArr1 = s.split(";");
                double x1 = Double.parseDouble(canArr1[0]);
                double y1 = Double.parseDouble(canArr1[1]);

                for (String value : canList1) {
                    String[] canArr2 = value.split(";");
                    double x2 = Double.parseDouble(canArr2[0]);
                    double y2 = Double.parseDouble(canArr2[1]);

                    cDis.add(CoorTrans.GetDistance(x1, y1, x2, y2));
                }
            }
            cDisList.add(cDis);
        }
        return cDisList;
    }

    public static void calResultsCoords(List<List<Double>> probListList, List<List<String>> candidateCoords) {
        List<Double> doubleList = new ArrayList<>();//最大概率
        List<String> stringList = new ArrayList<>();//最大概率对应候选点坐标
        for (int i = 0; i < probListList.size(); i++) {
            List<Double> dList = probListList.get(i);
            Double temp = Collections.max(dList);
            int flag = probListList.get(i).indexOf(temp);
            Double d =  probListList.get(i).get(flag);
            String s = candidateCoords.get(i).get(flag);
            doubleList.add(d);
            stringList.add(s);
        }
        for (int i = 0; i < candidateCoords.size(); i++) {
            SaveAsTxt.exportTxt3(candidateCoords.get(i),i);
        }
        SaveAsTxt.exportTxt4(stringList);
    }
}
